package com.supermap.dataservice.advisoryinfo.dao;

import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.advisoryinfo.entity.AdvAllInfo;
import com.supermap.dubbo.api.advisoryinfo.entity.AdvComment;
import com.supermap.dubbo.api.advisoryinfo.entity.Advisoryinfo;
import com.supermap.dubbo.api.advisoryinfo.entity.Advpraise;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @Author:langhongshuai
 * @Description:咨询数据访问层
 * @Date:18/1/28 下午11:57
 */
@Mapper
public interface AdvisoryinfoDao {
    /**
     * 获得用户的咨询信息
     * @return
     */
    @Select("select * from t_advisoryinfo t1 \n" +
            "inner join t_user t2 on t1.userId=t2.id\n"+
            " where  IF(ISNULL(#{areaCode}),true,t1.areacode like #{areaCode})")
    public List<Advisoryinfo> getAdvisoryInfoByUIdArea(@Param("areaCode") String areaCode);

    /**
     *  增加咨询
     * @param advisoryinfo
     */
    @Insert("Insert into t_advisoryinfo(userId,areacode,question,subjectid,farmlandid,lat,lon,development,expertId) \n" +
            "values(#{Advisory.userId},#{Advisory.areacode},#{Advisory.question}\n" +
            ",#{Advisory.subjectid},#{Advisory.farmlandid},#{Advisory.lat},#{Advisory.lon},#{Advisory.development},#{Advisory.expertId})")
    public void addAdvisory(@Param("Advisory") Advisoryinfo advisoryinfo);

    /**
     * 返回id
     * */
    @Select("SELECT LAST_INSERT_ID()")
    int getAdvInsertId();
    /**
     * 插入图片
     * */
    @Insert("     insert into t_advinfoimg(advInfoId,imageURLContent) values(#{advInfoId},#{imgUrl})")
    int addAdvImg(@Param("advInfoId")int advInfoId,@Param("imgUrl")String imgUrl);

    /**
     * 查询所有咨询
     * @return
     */
    @Select("select * from t_advisoryinfo t1 " +
            "inner join t_user t2 on t1.userId=t2.id")
    List<Advisoryinfo> getAdvisoryInfo();

    /**
     * 根据咨询ID 得到对应的点赞数
     * @param advInfoId
     * @return
     */
    @Select("select COUNT(*) from t_advpraise where advInfoId=#{advInfoId}")
    int getAdvpraiseCount(@Param("advInfoId") int advInfoId);

    /**
     * 根据userid和agrInfoId判断改用用户对改咨询点过赞
     * @param userId
     * @param advInfoId
     * @return
     */
    @Select("select isPraise from t_advpraise  where userId=#{userId} and advInfoId=#{advInfoId}")
    String  getAdvpraise(@Param("userId") int userId, @Param("advInfoId") int advInfoId);

    @Delete("delete from t_advpraise where  userId=#{userId} and advInfoId=#{advInfoId}")
    int DelAdvById(@Param("userId") int userId, @Param("advInfoId") int advInfoId);

    /**
     * 点击赞 增加一条数据
     * @param advpraise
     * @return
     */
    @Insert("Insert into t_advpraise(advInfoId,isPraise,userId) "+"values(#{advpraise.advInfoId},#{advpraise.isPraise},#{advpraise.userId}"+")")
    int addAdvpraise(@Param("advpraise") Advpraise advpraise);

    /**
     * 根据咨询ID得到对应所有评论
     * @param advInfoId
     * @return
     */
    @Select("select t1.*,t2.username from t_advcomment t1 inner JOIN t_user t2 on t2.id=t1.userId where t1.advInfoId=#{advInfoId}")
    List<AdvComment> getAdvcommentListByAdvInfoId(@Param("advInfoId") int advInfoId);

    /**
     * 增添评论
     * @param advComment
     * @return
     */
    @Insert("Insert into t_advcomment(advInfoId,comment,parentId,userId) "+"values(#{advComment.advInfoId},#{advComment.comment},#{advComment.parentId},#{advComment.userId}"+")")
    int addAdvcomment(@Param("advComment") AdvComment advComment);

    /**
     * 把未读的评论添加到表中
     * @param userid
     * @param typeid
     * @param type
     * @param comment
     * @param state
     * @return
     */
    @Insert("Insert into t_newmessage(userid,typeid,type,comment,state,uid) "
    +"values(#{userid},#{typeid},#{type},#{comment},#{state},#{uid})")
    int addNewMessage(@Param("userid") int userid, @Param("typeid") int typeid, @Param("type") int type, @Param("comment") String comment, @Param("state") int state, @Param("uid") String uid);

    /**
     * 删除咨询
     * @param advId
     */
    @Delete("Delete from t_advisoryinfo where id=#{advId}")
    public void delAdvisoryinfo(@Param("advId") int advId);

    /**
     * areacode和subjectid得到咨询
     * @param areacode
     * @param subjectid
     * @return
     */
    @Select("select * from t_advisoryinfo where areacode=#{areacode} and subjectid=#{subjectid} ")
   List<Advisoryinfo>  getAdvinfoByAreacodeandCropid(@Param("areacode") String areacode, @Param("subjectid") String subjectid);

    /**
     * 咨询 点赞 评论返回前端的信息
     * @param table
     * @param userId
     */
    @Select("select t1.id,t1.lon,t1.`lat`,t1.question,t2.username as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,group_concat(distinct t4.imageURLContent) as publisherAdvImg,t1.uploadTime as publisherTime,t1.development as development,t3.name AS cropName " +
            " from  t_advisoryinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id INNER JOIN t_crop t3 ON t1.`subjectid`=t3.id " +
            "  Left join t_advinfoimg t4 on t4.advInfoId =t1.id  "+
            " where t1.userId=#{userId}  and t1.del_flg=0 " +
            "   group by t1.id ORDER BY t1.uploadTime desc limit #{table.startPoint},#{table.limit} ")
    List<AdvAllInfo> getAdvAllInfoByUserId(@Param("table") Table table,@Param("userId")String userId);

    /**
     * 根据区域areaCode返回咨询 点赞 评论返回前端的信息
     * @param table
     * @param areaCode
     * @return
     */
//    @Select("select t1.id,t1.question,t2.username as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,t1.img_url as publisherAdvImg,t1.uploadTime as publisherTime " +
//            " from  t_advisoryinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id " +
//            " where  IF(ISNULL(#{areaCode}),true,t1.areacode like #{areaCode})  and  IF(ISNULL(#{subjectId}),true,t1.subjectid= #{subjectId}) and t1.del_flg=0 ORDER BY t1.uploadTime desc limit #{table.startPoint},#{table.limit} ")
    @SelectProvider(type = AdvSql.class, method = "getAdvAllInfoByareaCodeSql")
    List<AdvAllInfo> getAdvAllInfoByareaCode(@Param("table") Table table,@Param("areaCode")String areaCode,@Param("subjectId")String subjectId,@Param("expertId")String expertId);


    /**
     * 根据用户ID查询咨询总数
     * @param table
     * @param userId
     * @return
     */
    @Select("select count(*) from  t_advisoryinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id  INNER JOIN t_crop t3 ON t1.`subjectid`=t3.id \n" +
            "where t1.userId=#{userId}  and t1.del_flg=0 ORDER BY t1.uploadTime desc")
    int getAdvAllCountByUserId(@Param("table") Table table,@Param("userId")String userId);

    /**
     * 根据areaCode查询咨询总数
     * @param table
     * @param areaCode
     * @return
     */
//    @Select("select count(*) from  t_advisoryinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id \n" +
//            "where IF(ISNULL(#{areaCode}),true,t1.areacode like #{areaCode})  and  IF(ISNULL(#{subjectId}),true,t1.subjectid= #{subjectId})  and t1.del_flg=0 ORDER BY t1.uploadTime desc")
    @SelectProvider(type = AdvSql.class, method = "getAdvAllCountByAreaCodeSql")
    int getAdvAllCountByAreaCode(@Param("table") Table table,@Param("areaCode")String areaCode,@Param("subjectId")String subjectId,@Param("expertId")String expertId);

    /**
     * 根据userId和subjectid查询相关咨询
     * @param userId
     * @param subjectid
     * @return
     */
    @Select("SELECT * FROM t_advisoryinfo WHERE IF(isnull(#{userId}),true,userId=#{userId}) and IF(isnull(#{subjectid}),true,subjectid=#{subjectid})")
    List<Advisoryinfo> getAdvByuserIdAndSubject(@Param("userId")String userId,@Param("subjectid")String subjectid);

    /**
     * 内部类 编写动态sql
     *
     * @author lxs
     */
    public class AdvSql {
        //按条件分页查询用户列表
        public String getAdvAllInfoByareaCodeSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            String areaCode = (String)map.get("areaCode");
            String subjectId = (String)map.get("subjectId");
            String expertId = (String)map.get("expertId");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("select t1.id,t1.lon,t1.`lat`,t1.question,t2.username as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,group_concat(distinct t4.imageURLContent) as publisherAdvImg,t1.uploadTime as publisherTime ,t1.development as development,t3.name AS cropName "+
                  " from  t_advisoryinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id INNER JOIN t_crop t3 ON t1.`subjectid`=t3.id ");
            sql.append(" and t1.del_flg=0 ");
            if(areaCode !=null){
                sql.append(" and t1.areacode like '"+areaCode+"%'");
            }
            if(subjectId !=null){
                sql.append(" and t1.subjectid = "+subjectId);
            }
            if(expertId !=null){
                sql.append(" and t1.expertId = "+expertId);
            }
            sql.append(" Left join t_advinfoimg t4 on t4.advInfoId =t1.id \n" +
                    "group by t1.id ");

            sql.append(" ORDER BY t1.uploadTime desc limit "+table.getStartPoint() + "," + table.getLimit());
            return sql.toString();
        }

        public String getAdvAllCountByAreaCodeSql(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            String areaCode = (String)map.get("areaCode");
            String subjectId = (String)map.get("subjectId");
            String expertId = (String)map.get("expertId");
            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("select count(*) "+
                    " from  t_advisoryinfo t1 INNER JOIN t_user t2 on t1.userId=t2.id INNER JOIN t_crop t3 ON t1.`subjectid`=t3.id ");
            sql.append(" and t1.del_flg=0 ");
            if(areaCode !=null){
                sql.append(" and t1.areacode like '"+areaCode+"%'");
            }
            if(subjectId !=null){
                sql.append(" and t1.subjectid = "+subjectId);
            }
            if(expertId !=null){
                sql.append(" and t1.expertId = "+expertId);
            }
            sql.append(" ORDER BY t1.uploadTime desc");
            return sql.toString();
        }

    }

}
